11 - Spatial & Temporal Data

:::

What is the area of Northern Ireland?

SELECT ST_AREA(boundpath)*1406 
    as "Area (km^2)" from political_unit JOIN boundary 
ON unitname = 'Northern Ireland'
WHERE political_unit.unitcode = boundary.unitcode;

How close is the computed value to that reported in Wikipedia?

Wikipedia reports 13,843 km2. A finer grid would increase accuracy.

What is the eastern most city in Northern Ireland?

SELECT east.cityname FROM city east JOIN political_unit
ON east.unitcode = political_unit.unitcode 
WHERE unitname = 'Northern Ireland' 
AND NOT EXISTS    
(SELECT * FROM city other WHERE ST_X(other.cityloc) > ST_X(east.cityloc));

Modify the example geometry database design to include

  • Historic buildings in a city
  • Walking paths in a city
  • Use of the MULTIPOLYGON data type to indicate a political region’s boundary

CREATE TABLE political_unit (
  unitname          VARCHAR(30),
  unitcode          CHAR(2),
  unitpop           DECIMAL(6,2),
  boundaries        MULTIPOLYGON NOT NULL,
  PRIMARY KEY (unitcode));

CREATE TABLE city (
  cityname          VARCHAR(30),
  cityloc           POINT NOT NULL,
  unitcode          CHAR(2),
  historicbuildings MULTIPOINT NULL,
  walkingpaths      MULTILINESTRING NULL,
  PRIMARY KEY (cityname, unitcode),
    CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
        REFERENCES political_unit(unitcode));